import { sqlUtil } from '../common/db.js';

const NoticeboardModel = {
    // 获取帖子列表
    async getList(query, page) {
        let sql = `
            SELECT p.*, t.Name as TagName, s.Name as AuthorName, s.Photo as AuthorAvatar,
                   CASE WHEN l.StudentID IS NOT NULL THEN 1 ELSE 0 END as IsLiked
            FROM noticeboardPosts p
            LEFT JOIN tags t ON p.TagID = t.TagID
            LEFT JOIN students s ON p.StudentID = s.StudentID
            LEFT JOIN likes l ON p.PostID = l.PostID 
                AND l.StudentID = ? AND l.IsDeleted = 0
            WHERE p.IsDeleted = 0
        `;
        let params = [query.studentId];

        if (query.keyword) {
            sql += ` AND (
                LOWER(p.Title) LIKE ? OR 
                LOWER(p.Content) LIKE ?
            )`;
            const keyword = `%${query.keyword.toLowerCase()}%`;
            params.push(keyword, keyword);
        }

        if (query.tagId) {
            sql += ` AND p.TagID = ?`;
            params.push(query.tagId);
        }

        if (query.filter === 'popular') {
            sql += ` AND p.IsHot = 1`;
        } else if (query.filter === 'mypost') {
            sql += ` AND p.StudentID = ?`;
            params.push(query.studentId);
        }

        sql += ` ORDER BY p.CreateTime DESC`;
        return sqlUtil.getLimit(sql, params, page);
    },

    // 获取帖子详情
    async getById(postId, studentId) {
        const sql = `
            SELECT p.*, t.Name as TagName, s.Name as AuthorName, s.Photo as AuthorAvatar,
                   CASE WHEN l.StudentID IS NOT NULL THEN 1 ELSE 0 END as IsLiked
            FROM noticeboardPosts p
            LEFT JOIN tags t ON p.TagID = t.TagID
            LEFT JOIN students s ON p.StudentID = s.StudentID
            LEFT JOIN likes l ON p.PostID = l.PostID 
                AND l.StudentID = ? AND l.IsDeleted = 0
            WHERE p.PostID = ? AND p.IsDeleted = 0
        `;
        return sqlUtil.get(sql, [studentId, postId]);
    },

    // 创建帖子
    async create(post) {
        const sql = `
            INSERT INTO noticeboardPosts (Title, Content, StudentID, TagID, Media)
            VALUES (?, ?, ?, ?, ?)
        `;
        return sqlUtil.run(sql, [
            post.title,
            post.content,
            post.studentId,
            post.tagId,
            post.media
        ]);
    },

    // 更新帖子
    async update(postId, post) {
        const sql = `
            UPDATE noticeboardPosts 
            SET Title = ?, Content = ?, TagID = ?, Media = ?, UpdateTime = CURRENT_TIMESTAMP
            WHERE PostID = ? AND IsDeleted = 0
        `;
        return sqlUtil.run(sql, [
            post.title,
            post.content,
            post.tagId,
            post.media,
            postId
        ]);
    },

    // 删除帖子
    async delete(postId) {
        const sql = `UPDATE noticeboardPosts SET IsDeleted = 1 WHERE PostID = ?`;
        return sqlUtil.run(sql, [postId]);
    },

    // 点赞/取消点赞
    async toggleLike(postId, studentId) {
        await sqlUtil.withTransaction(async () => {
            // 检查是否已点赞
            const existingSql = `
                SELECT IsDeleted FROM likes 
            WHERE PostID = ? AND StudentID = ?
        `;
        const existing = await sqlUtil.get(existingSql, [postId, studentId]);

        if (!existing) {
            // 新增点赞
            const insertSql = `
                INSERT INTO likes (PostID, StudentID)
                VALUES (?, ?)
            `;
            await sqlUtil.run(insertSql, [postId, studentId]);
            await this.updateLikeCount(postId, 1);
            return true;
        } else {
            // 更新点赞状态
            const newStatus = existing.IsDeleted ? 0 : 1;
            const updateSql = `
                UPDATE likes 
                SET IsDeleted = ?
                WHERE PostID = ? AND StudentID = ?
            `;
            await sqlUtil.run(updateSql, [newStatus, postId, studentId]);
            await this.updateLikeCount(postId, newStatus ? -1 : 1);
            return !newStatus;
        }
    },

    // 更新点赞数
    async updateLikeCount(postId, increment) {
        const sql = `
            UPDATE noticeboardPosts 
            SET LikeCount = LikeCount + ?
            WHERE PostID = ?
        `;
        return sqlUtil.run(sql, [increment, postId]);
    },

    // 获取帖子评论
    async getComments(postId) {
        const sql = `
            SELECT c.*, s.Name as AuthorName, s.Photo as AuthorAvatar
            FROM comments c
            LEFT JOIN students s ON c.StudentID = s.StudentID
            WHERE c.PostID = ? AND c.IsDeleted = 0
            ORDER BY c.CreateTime DESC
        `;
        return sqlUtil.all(sql, [postId]);
    },

    // 添加评论
    async addComment(postId, content, studentId) {
        const sql = `
            INSERT INTO comments (PostID, StudentID, Content)
            VALUES (?, ?, ?)
        `;
        const result = await sqlUtil.run(sql, [postId, studentId, content]);
        
        // 更新帖子评论数
        await this.updateCommentCount(postId);
        
        return result;
    },

    // 删除评论
    async deleteComment(commentId, studentId) {
        const sql = `
            UPDATE comments 
            SET IsDeleted = 1 
            WHERE CommentID = ? AND StudentID = ?
        `;
        const result = await sqlUtil.run(sql, [commentId, studentId]);
        
        // 更新帖子评论数
        const post = await this.getCommentPost(commentId);
        if (post) {
            await this.updateCommentCount(post.PostID);
        }
        
        return result;
    },

    // 获取评论所属的帖子
    async getCommentPost(commentId) {
        const sql = `
            SELECT PostID 
            FROM comments 
            WHERE CommentID = ?
        `;
        return sqlUtil.get(sql, [commentId]);
    },

    // 更新帖子评论数
    async updateCommentCount(postId) {
        const sql = `
            UPDATE noticeboardPosts
            SET CommentCount = (
                SELECT COUNT(*) 
                FROM comments 
                WHERE PostID = ? AND IsDeleted = 0
            )
            WHERE PostID = ?
        `;
        return sqlUtil.run(sql, [postId, postId]);
    },

    // 当帖子评论超过10条或点赞超过10或被管理员设置时，将帖子设置为热门帖子
    async setHotPost(postId) {
        const sql = `UPDATE noticeboardPosts SET IsHot = 1 WHERE PostID = ?`;
        return sqlUtil.run(sql, [postId]);
    }
};

export default NoticeboardModel;
